{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.网页类型分析\n",
    "## 1.1 网页类型统计\n",
    "### 1.1.1 连接数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\pymysql\\cursors.py:170: Warning: (1366, \"Incorrect string value: '\\\\xD6\\\\xD0\\\\xB9\\\\xFA\\\\xB1\\\\xEA...' for column 'VARIABLE_VALUE' at row 478\")\n",
      "  result = self._query(query)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'\\n用create_engine建立连接，连接地址的意思依次为“数据库格式（mysql）+程序名（pymysql）+账号密码@地址端口/数据库名（test）”，最后指定编码为utf8；\\nall_gzdata是表名，engine是连接数据的引擎，chunksize指定每次读取1万条记录。这时候sql是一个容器，未真正读取数据。\\n'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:@localhost/wangye?charset=utf8')\n",
    "sql = pd.read_sql('all_gzdata', engine, chunksize = 10000)\n",
    "'''\n",
    "用create_engine建立连接，连接地址的意思依次为“数据库格式（mysql）+程序名（pymysql）+账号密码@地址端口/数据库名（test）”，最后指定编码为utf8；\n",
    "all_gzdata是表名，engine是连接数据的引擎，chunksize指定每次读取1万条记录。这时候sql是一个容器，未真正读取数据。\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.2 统计fullURLId的数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts = [ i['fullURLId'].value_counts() for i in sql] #按次10000存取，逐块统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101001</td>\n",
       "      <td>633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101002</td>\n",
       "      <td>983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101003</td>\n",
       "      <td>49431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101004</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101005</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101006</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101007</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101008</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101009</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>102001</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>102002</td>\n",
       "      <td>2164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>102003</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>102004</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>102005</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>102006</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>102007</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>102008</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>102009</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>103002</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>103003</td>\n",
       "      <td>292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>106001</td>\n",
       "      <td>664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>107001</td>\n",
       "      <td>24707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1999001</td>\n",
       "      <td>26069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>301001</td>\n",
       "      <td>2068</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      index    num\n",
       "0    101001    633\n",
       "1    101002    983\n",
       "2    101003  49431\n",
       "3    101004     19\n",
       "4    101005      3\n",
       "5    101006     14\n",
       "6    101007     17\n",
       "7    101008     33\n",
       "8    101009    107\n",
       "9    102001    215\n",
       "10   102002   2164\n",
       "11   102003    152\n",
       "12   102004     35\n",
       "13   102005     42\n",
       "14   102006     19\n",
       "15   102007     31\n",
       "16   102008     30\n",
       "17   102009     26\n",
       "18   103002     21\n",
       "19   103003    292\n",
       "20   106001    664\n",
       "21   107001  24707\n",
       "22  1999001  26069\n",
       "23   301001   2068"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counts = pd.concat(counts).groupby(level=0).sum() #合并统计结果，把相同的统计项合并（即按index分组并求和）\n",
    "counts = counts.reset_index() #重新设置index，将原来的index作为counts的一列。\n",
    "counts.columns = ['index', 'num'] #重新设置列名，主要是第二列，默认为0\n",
    "counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.3 提取前3个数字做网页类别"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>num</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101001</td>\n",
       "      <td>633</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101002</td>\n",
       "      <td>983</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101003</td>\n",
       "      <td>49431</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101004</td>\n",
       "      <td>19</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101005</td>\n",
       "      <td>3</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101006</td>\n",
       "      <td>14</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101007</td>\n",
       "      <td>17</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101008</td>\n",
       "      <td>33</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101009</td>\n",
       "      <td>107</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>102001</td>\n",
       "      <td>215</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>102002</td>\n",
       "      <td>2164</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>102003</td>\n",
       "      <td>152</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>102004</td>\n",
       "      <td>35</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>102005</td>\n",
       "      <td>42</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>102006</td>\n",
       "      <td>19</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>102007</td>\n",
       "      <td>31</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>102008</td>\n",
       "      <td>30</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>102009</td>\n",
       "      <td>26</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>103002</td>\n",
       "      <td>21</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>103003</td>\n",
       "      <td>292</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>106001</td>\n",
       "      <td>664</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>107001</td>\n",
       "      <td>24707</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1999001</td>\n",
       "      <td>26069</td>\n",
       "      <td>199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>301001</td>\n",
       "      <td>2068</td>\n",
       "      <td>301</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      index    num type\n",
       "0    101001    633  101\n",
       "1    101002    983  101\n",
       "2    101003  49431  101\n",
       "3    101004     19  101\n",
       "4    101005      3  101\n",
       "5    101006     14  101\n",
       "6    101007     17  101\n",
       "7    101008     33  101\n",
       "8    101009    107  101\n",
       "9    102001    215  102\n",
       "10   102002   2164  102\n",
       "11   102003    152  102\n",
       "12   102004     35  102\n",
       "13   102005     42  102\n",
       "14   102006     19  102\n",
       "15   102007     31  102\n",
       "16   102008     30  102\n",
       "17   102009     26  102\n",
       "18   103002     21  103\n",
       "19   103003    292  103\n",
       "20   106001    664  106\n",
       "21   107001  24707  107\n",
       "22  1999001  26069  199\n",
       "23   301001   2068  301"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counts['type'] = counts['index'].str.extract('(\\d{3})') #提取前三个数字作为类别id\n",
    "counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>percentage</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>51240</td>\n",
       "      <td>47.543493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2714</td>\n",
       "      <td>2.518209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>313</td>\n",
       "      <td>0.290420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>664</td>\n",
       "      <td>0.616098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>24707</td>\n",
       "      <td>22.924611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>26069</td>\n",
       "      <td>24.188355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>2068</td>\n",
       "      <td>1.918812</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        num  percentage\n",
       "type                   \n",
       "101   51240   47.543493\n",
       "102    2714    2.518209\n",
       "103     313    0.290420\n",
       "106     664    0.616098\n",
       "107   24707   22.924611\n",
       "199   26069   24.188355\n",
       "301    2068    1.918812"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counts_ = counts[['type', 'num']].groupby('type').sum() #按类别合并\n",
    "counts_.sort_values('num', ascending = False) #降序排列\n",
    "counts_['percentage'] = (counts_['num']/counts_['num'].sum())*100\n",
    "counts_.to_excel('./tmp/1_1_3type_counts.xlsx')\n",
    "counts_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 各网页类型中小类别占比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        index    num\n",
      "type                \n",
      "101    101001    633\n",
      "101    101002    983\n",
      "101    101003  49431\n",
      "101    101004     19\n",
      "101    101005      3\n",
      "101    101006     14\n",
      "101    101007     17\n",
      "101    101008     33\n",
      "101    101009    107\n",
      "102    102001    215\n",
      "102    102002   2164\n",
      "102    102003    152\n",
      "102    102004     35\n",
      "102    102005     42\n",
      "102    102006     19\n",
      "102    102007     31\n",
      "102    102008     30\n",
      "102    102009     26\n",
      "103    103002     21\n",
      "103    103003    292\n",
      "106    106001    664\n",
      "107    107001  24707\n",
      "199   1999001  26069\n",
      "301    301001   2068\n",
      "        num\n",
      "type       \n",
      "101   51240\n",
      "102    2714\n",
      "103     313\n",
      "106     664\n",
      "107   24707\n",
      "199   26069\n",
      "301    2068\n",
      "        index  num_x  num_y\n",
      "type                       \n",
      "101    101001    633  51240\n",
      "101    101002    983  51240\n",
      "101    101003  49431  51240\n",
      "101    101004     19  51240\n",
      "101    101005      3  51240\n",
      "101    101006     14  51240\n",
      "101    101007     17  51240\n",
      "101    101008     33  51240\n",
      "101    101009    107  51240\n",
      "102    102001    215   2714\n",
      "102    102002   2164   2714\n",
      "102    102003    152   2714\n",
      "102    102004     35   2714\n",
      "102    102005     42   2714\n",
      "102    102006     19   2714\n",
      "102    102007     31   2714\n",
      "102    102008     30   2714\n",
      "102    102009     26   2714\n",
      "103    103002     21    313\n",
      "103    103003    292    313\n",
      "106    106001    664    664\n",
      "107    107001  24707  24707\n",
      "199   1999001  26069  26069\n",
      "301    301001   2068   2068\n"
     ]
    }
   ],
   "source": [
    "# 每个大类别下面小类别占比\n",
    "a = counts.set_index(['type'])\n",
    "print(a)\n",
    "b = counts.groupby('type').sum()\n",
    "print(b)\n",
    "c = pd.merge(a,b,left_index=True,right_index=True)\n",
    "print(c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>num</th>\n",
       "      <th>persentage</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101001</td>\n",
       "      <td>633</td>\n",
       "      <td>1.235363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101002</td>\n",
       "      <td>983</td>\n",
       "      <td>1.918423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101003</td>\n",
       "      <td>49431</td>\n",
       "      <td>96.469555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101004</td>\n",
       "      <td>19</td>\n",
       "      <td>0.037080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101005</td>\n",
       "      <td>3</td>\n",
       "      <td>0.005855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101006</td>\n",
       "      <td>14</td>\n",
       "      <td>0.027322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101007</td>\n",
       "      <td>17</td>\n",
       "      <td>0.033177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101008</td>\n",
       "      <td>33</td>\n",
       "      <td>0.064403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101009</td>\n",
       "      <td>107</td>\n",
       "      <td>0.208821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102001</td>\n",
       "      <td>215</td>\n",
       "      <td>7.921887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102002</td>\n",
       "      <td>2164</td>\n",
       "      <td>79.734709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102003</td>\n",
       "      <td>152</td>\n",
       "      <td>5.600590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102004</td>\n",
       "      <td>35</td>\n",
       "      <td>1.289609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102005</td>\n",
       "      <td>42</td>\n",
       "      <td>1.547531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102006</td>\n",
       "      <td>19</td>\n",
       "      <td>0.700074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102007</td>\n",
       "      <td>31</td>\n",
       "      <td>1.142225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102008</td>\n",
       "      <td>30</td>\n",
       "      <td>1.105380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102009</td>\n",
       "      <td>26</td>\n",
       "      <td>0.957996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>103002</td>\n",
       "      <td>21</td>\n",
       "      <td>6.709265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>103003</td>\n",
       "      <td>292</td>\n",
       "      <td>93.290735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>106001</td>\n",
       "      <td>664</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>107001</td>\n",
       "      <td>24707</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>1999001</td>\n",
       "      <td>26069</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>301001</td>\n",
       "      <td>2068</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        index    num  persentage\n",
       "type                            \n",
       "101    101001    633    1.235363\n",
       "101    101002    983    1.918423\n",
       "101    101003  49431   96.469555\n",
       "101    101004     19    0.037080\n",
       "101    101005      3    0.005855\n",
       "101    101006     14    0.027322\n",
       "101    101007     17    0.033177\n",
       "101    101008     33    0.064403\n",
       "101    101009    107    0.208821\n",
       "102    102001    215    7.921887\n",
       "102    102002   2164   79.734709\n",
       "102    102003    152    5.600590\n",
       "102    102004     35    1.289609\n",
       "102    102005     42    1.547531\n",
       "102    102006     19    0.700074\n",
       "102    102007     31    1.142225\n",
       "102    102008     30    1.105380\n",
       "102    102009     26    0.957996\n",
       "103    103002     21    6.709265\n",
       "103    103003    292   93.290735\n",
       "106    106001    664  100.000000\n",
       "107    107001  24707  100.000000\n",
       "199   1999001  26069  100.000000\n",
       "301    301001   2068  100.000000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c['persentage'] = (c['num_x']/c['num_y'])*100\n",
    "del c['num_y']\n",
    "c.rename(columns={'num_x':'num'},inplace=True)\n",
    "c"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.4 网页107类型中的内部统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\pymysql\\cursors.py:170: Warning: (1366, \"Incorrect string value: '\\\\xD6\\\\xD0\\\\xB9\\\\xFA\\\\xB1\\\\xEA...' for column 'VARIABLE_VALUE' at row 478\")\n",
      "  result = self._query(query)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "知识内容页    22566\n",
      "知识列表页     1282\n",
      "知识首页       859\n",
      "Name: type, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:@localhost/wangye?charset=utf8')\n",
    "sql = pd.read_sql('all_gzdata', engine, chunksize = 10000)\n",
    "\n",
    "#统计107类别的情况\n",
    "def count107(i): #自定义统计函数\n",
    "  j = i[['fullURL']][i['fullURLId'].str.contains('107')].copy() #找出类别包含107的网址\n",
    "  j['type'] = None #添加空列\n",
    "  j['type'][j['fullURL'].str.contains('info/.+?/')] = u'知识首页' #info以/结尾\n",
    "  j['type'][j['fullURL'].str.contains('info/.+?/.+?')] = u'知识列表页'\n",
    "  j['type'][j['fullURL'].str.contains('/\\d+?_*\\d+?\\.html')] = u'知识内容页'\n",
    "  return j['type'].value_counts()\n",
    "\n",
    "counts2 = [count107(i) for i in sql] #逐块统计\n",
    "counts2 = pd.concat(counts2).groupby(level=0).sum() #合并统计结果\n",
    "\n",
    "counts2.to_excel('./tmp/1_4type107.xlsx')\n",
    "print(counts2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.点击次数分析\n",
    "## 2.1 查看IP的出现次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        realIP  1\n",
      "226318       2  1\n",
      "270457       1  1\n",
      "503676       1  1\n",
      "611448       2  1\n",
      "857460       1  1\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:@localhost/wangye?charset=utf8')\n",
    "sql = pd.read_sql('all_gzdata', engine, chunksize = 10000)\n",
    "\n",
    "#统计点击次数\n",
    "c = [i['realIP'].value_counts() for i in sql] #分块统计各个IP的出现次数\n",
    "count3 = pd.concat(c).groupby(level = 0).sum() #合并统计结果，level=0表示按index分组\n",
    "count_df = pd.DataFrame(count3) #Series转为DataFrame\n",
    "count3=count_df\n",
    "count3[1]=1 # 添加1列全为1\n",
    "print(count3.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 点击次数与用户数量关系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "107775\n",
      "            1\n",
      "realIP       \n",
      "1       23861\n",
      "2        6941\n",
      "3        2568\n",
      "4        1388\n",
      "5         804\n",
      "        用户数\n",
      "点击次数       \n",
      "1     23861\n",
      "2      6941\n",
      "3      2568\n",
      "4      1388\n",
      "5       804\n"
     ]
    }
   ],
   "source": [
    "realIP_sum=count3['realIP'].sum()\n",
    "print(realIP_sum)\n",
    "                                 \n",
    "count3= count3.groupby('realIP').sum()##统计各个“不同点击次数”分别出现的次数# 也可以使用counts1_['realIP'].value_counts()功能\n",
    "\n",
    "print(count3.head())\n",
    "\n",
    "                                 \n",
    "count3.columns=[u'用户数']\n",
    "count3.index.name = u'点击次数'\n",
    "\n",
    "print(count3.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2.1 用户百分比，点击记录百分比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>点击次数</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>用户数</th>\n",
       "      <td>23861.000000</td>\n",
       "      <td>6941.000000</td>\n",
       "      <td>2568.000000</td>\n",
       "      <td>1388.000000</td>\n",
       "      <td>804.000000</td>\n",
       "      <td>507.000000</td>\n",
       "      <td>323.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户百分比</th>\n",
       "      <td>62.722780</td>\n",
       "      <td>18.245623</td>\n",
       "      <td>6.750434</td>\n",
       "      <td>3.648599</td>\n",
       "      <td>2.113454</td>\n",
       "      <td>1.332738</td>\n",
       "      <td>0.849062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>点击记录百分比</th>\n",
       "      <td>22.139643</td>\n",
       "      <td>12.880538</td>\n",
       "      <td>7.148225</td>\n",
       "      <td>5.151473</td>\n",
       "      <td>3.729993</td>\n",
       "      <td>2.822547</td>\n",
       "      <td>2.097889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "点击次数                1            2            3            4           5  \\\n",
       "用户数      23861.000000  6941.000000  2568.000000  1388.000000  804.000000   \n",
       "用户百分比       62.722780    18.245623     6.750434     3.648599    2.113454   \n",
       "点击记录百分比     22.139643    12.880538     7.148225     5.151473    3.729993   \n",
       "\n",
       "点击次数              6           7  \n",
       "用户数      507.000000  323.000000  \n",
       "用户百分比      1.332738    0.849062  \n",
       "点击记录百分比    2.822547    2.097889  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count3[u'用户百分比'] = count3[u'用户数']/count3[u'用户数'].sum()*100\n",
    "count3[u'点击记录百分比'] = count3[u'用户数']*count3.index/realIP_sum*100\n",
    "count3.sort_index(inplace = True)\n",
    "c=count3.iloc[:7,]\n",
    "c=c.T\n",
    "c"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2.2 浏览七次以上数据合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>点击次数</th>\n",
       "      <th>总计</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>7次以上</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>用户数</th>\n",
       "      <td>38042</td>\n",
       "      <td>23861.000000</td>\n",
       "      <td>6941.000000</td>\n",
       "      <td>2568.000000</td>\n",
       "      <td>1388.000000</td>\n",
       "      <td>804.000000</td>\n",
       "      <td>507.000000</td>\n",
       "      <td>323.000000</td>\n",
       "      <td>1650.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户百分比</th>\n",
       "      <td>100</td>\n",
       "      <td>62.722780</td>\n",
       "      <td>18.245623</td>\n",
       "      <td>6.750434</td>\n",
       "      <td>3.648599</td>\n",
       "      <td>2.113454</td>\n",
       "      <td>1.332738</td>\n",
       "      <td>0.849062</td>\n",
       "      <td>4.337311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>点击记录百分比</th>\n",
       "      <td>100</td>\n",
       "      <td>22.139643</td>\n",
       "      <td>12.880538</td>\n",
       "      <td>7.148225</td>\n",
       "      <td>5.151473</td>\n",
       "      <td>3.729993</td>\n",
       "      <td>2.822547</td>\n",
       "      <td>2.097889</td>\n",
       "      <td>44.029691</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "点击次数        总计             1            2            3            4  \\\n",
       "用户数      38042  23861.000000  6941.000000  2568.000000  1388.000000   \n",
       "用户百分比      100     62.722780    18.245623     6.750434     3.648599   \n",
       "点击记录百分比    100     22.139643    12.880538     7.148225     5.151473   \n",
       "\n",
       "点击次数              5           6           7         7次以上  \n",
       "用户数      804.000000  507.000000  323.000000  1650.000000  \n",
       "用户百分比      2.113454    1.332738    0.849062     4.337311  \n",
       "点击记录百分比    3.729993    2.822547    2.097889    44.029691  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.insert(0,u'总计',[count3[u'用户数'].sum(),100,100])\n",
    "c[u'7次以上'] = c.iloc[:,0]- c.iloc[:,1:].sum(1)\n",
    "\n",
    "c.to_excel('./tmp/2_2_2clickTimes.xlsx')\n",
    "c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>点击次数</th>\n",
       "      <th>总计</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>7次以上</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>用户数</th>\n",
       "      <td>38042.00</td>\n",
       "      <td>23861.00</td>\n",
       "      <td>6941.00</td>\n",
       "      <td>2568.00</td>\n",
       "      <td>1388.00</td>\n",
       "      <td>804.00</td>\n",
       "      <td>507.00</td>\n",
       "      <td>323.00</td>\n",
       "      <td>1650.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户百分比</th>\n",
       "      <td>100.00</td>\n",
       "      <td>62.72</td>\n",
       "      <td>18.25</td>\n",
       "      <td>6.75</td>\n",
       "      <td>3.65</td>\n",
       "      <td>2.11</td>\n",
       "      <td>1.33</td>\n",
       "      <td>0.85</td>\n",
       "      <td>4.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>点击记录百分比</th>\n",
       "      <td>100.00</td>\n",
       "      <td>22.14</td>\n",
       "      <td>12.88</td>\n",
       "      <td>7.15</td>\n",
       "      <td>5.15</td>\n",
       "      <td>3.73</td>\n",
       "      <td>2.82</td>\n",
       "      <td>2.10</td>\n",
       "      <td>44.03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "点击次数           总计         1        2        3        4       5       6  \\\n",
       "用户数      38042.00  23861.00  6941.00  2568.00  1388.00  804.00  507.00   \n",
       "用户百分比      100.00     62.72    18.25     6.75     3.65    2.11    1.33   \n",
       "点击记录百分比    100.00     22.14    12.88     7.15     5.15    3.73    2.82   \n",
       "\n",
       "点击次数          7     7次以上  \n",
       "用户数      323.00  1650.00  \n",
       "用户百分比      0.85     4.34  \n",
       "点击记录百分比    2.10    44.03  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 转置表格，并将所有输出保留两位小数\n",
    "d = c.T\n",
    "format = lambda x: '%.2f' % x  # 也可以使用d.round(4)\n",
    "d = d.applymap(format)\n",
    "d.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2.3 浏览七次以上用户分析表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:13: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n",
      "  del sys.path[0]\n",
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:14: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n",
      "  \n",
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:15: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>点击次数</th>\n",
       "      <th>用户数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8~100</td>\n",
       "      <td>1601.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101~1000</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000以上</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       点击次数     用户数\n",
       "0     8~100  1601.0\n",
       "1  101~1000    47.0\n",
       "2    1000以上     2.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "# 分析浏览次数7次以上的数据\n",
    "times = count3.index[7:]# [8,    9,   10,   11,   12,   13,   14, ...]\n",
    "bins = [7,100,1000,50000]\n",
    "cats = pd.cut(times,bins,right=True,labels=['8~100','101~1000','1000以上'])\n",
    "#[8~100, 8~100, 8~100, 8~100, 8~100, ..., 101~1000, 101~1000, 101~1000, 1000以上, 1000以上]\n",
    "e = cats.value_counts()\n",
    "e = pd.DataFrame(e, columns =[u'用户数'])\n",
    "e.index.name = u'点击次数'\n",
    "\n",
    "e[u'用户数'] = np.nan\n",
    "e.ix[u'8~100',u'用户数'] = count3.loc[8:100,:][u'用户数'].sum()\n",
    "e.ix['101~1000',u'用户数'] = count3.loc[101:1000,:][u'用户数'].sum()\n",
    "e.ix['1000以上',u'用户数'] = count3.loc[1001:,:][u'用户数'].sum()\n",
    "e.sort_values(by=u'用户数',ascending=False,inplace = True)\n",
    "e.reset_index(inplace=True)\n",
    "e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 浏览一次的用户行为分析\n",
    "### 2.3.1 点击次数为一的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>点击次数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>realIP</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>270457</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503676</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>857460</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>993851</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1808142</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         点击次数\n",
       "realIP       \n",
       "270457      1\n",
       "503676      1\n",
       "857460      1\n",
       "993851      1\n",
       "1808142     1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取浏览一次的所有数据\n",
    "f = count_df[count_df['realIP']==1]\n",
    "del f[1]\n",
    "f.columns = [u'点击次数']\n",
    "f.index.name = 'realIP'\n",
    "f.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.2 合并数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\pymysql\\cursors.py:170: Warning: (1366, \"Incorrect string value: '\\\\xD6\\\\xD0\\\\xB9\\\\xFA\\\\xB1\\\\xEA...' for column 'VARIABLE_VALUE' at row 478\")\n",
      "  result = self._query(query)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>点击次数</th>\n",
       "      <th>fullURLId</th>\n",
       "      <th>fullURL</th>\n",
       "      <th>realIP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8355</th>\n",
       "      <td>1</td>\n",
       "      <td>101003</td>\n",
       "      <td>http://www.lawtime.cn/ask/question_2077376.html</td>\n",
       "      <td>270457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6285</th>\n",
       "      <td>1</td>\n",
       "      <td>1999001</td>\n",
       "      <td>http://www.lawtime.cn/ask/exp/13445.html</td>\n",
       "      <td>503676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7924</th>\n",
       "      <td>1</td>\n",
       "      <td>107001</td>\n",
       "      <td>http://www.lawtime.cn/info/hetong/htfanben/qit...</td>\n",
       "      <td>857460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4204</th>\n",
       "      <td>1</td>\n",
       "      <td>1999001</td>\n",
       "      <td>http://www.lawtime.cn/ask/exp/8191.html</td>\n",
       "      <td>993851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>1</td>\n",
       "      <td>101003</td>\n",
       "      <td>http://www.lawtime.cn/ask/question_379149.html</td>\n",
       "      <td>1808142</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      点击次数 fullURLId                                            fullURL  \\\n",
       "8355     1    101003    http://www.lawtime.cn/ask/question_2077376.html   \n",
       "6285     1   1999001           http://www.lawtime.cn/ask/exp/13445.html   \n",
       "7924     1    107001  http://www.lawtime.cn/info/hetong/htfanben/qit...   \n",
       "4204     1   1999001            http://www.lawtime.cn/ask/exp/8191.html   \n",
       "89       1    101003     http://www.lawtime.cn/ask/question_379149.html   \n",
       "\n",
       "       realIP  \n",
       "8355   270457  \n",
       "6285   503676  \n",
       "7924   857460  \n",
       "4204   993851  \n",
       "89    1808142  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:@localhost/wangye?charset=utf8')\n",
    "sql = pd.read_sql('all_gzdata', engine, chunksize = 10000)\n",
    "# g = [pd.merge(f,i[['fullURLId','fullURL','realIP']],right_on = 'realIP',left_index=True,how ='left') for i in sql]\n",
    "g = [i[['fullURLId','fullURL','realIP']] for i in sql]\n",
    "g = pd.concat(g)\n",
    "h = pd.merge(f,g,right_on = 'realIP',left_index=True,how ='left')\n",
    "h.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.4 浏览一次的用户的网页类型ID分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         fullURLId\n",
      "101003       17838\n",
      "107001        3930\n",
      "1999001       1952\n",
      "301001          99\n",
      "102001          12\n",
      "106001          10\n",
      "101002           9\n",
      "101001           5\n",
      "102002           3\n",
      "101009           2\n",
      "103003           1\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>个数</th>\n",
       "      <th>百分比</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>网页类型ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101003</th>\n",
       "      <td>17838</td>\n",
       "      <td>74.757973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107001</th>\n",
       "      <td>3930</td>\n",
       "      <td>16.470391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999001</th>\n",
       "      <td>1952</td>\n",
       "      <td>8.180713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301001</th>\n",
       "      <td>99</td>\n",
       "      <td>0.414903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102001</th>\n",
       "      <td>12</td>\n",
       "      <td>0.050291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106001</th>\n",
       "      <td>10</td>\n",
       "      <td>0.041909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101002</th>\n",
       "      <td>9</td>\n",
       "      <td>0.037718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101001</th>\n",
       "      <td>5</td>\n",
       "      <td>0.020955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102002</th>\n",
       "      <td>3</td>\n",
       "      <td>0.012573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101009</th>\n",
       "      <td>2</td>\n",
       "      <td>0.008382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103003</th>\n",
       "      <td>1</td>\n",
       "      <td>0.004191</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            个数        百分比\n",
       "网页类型ID                   \n",
       "101003   17838  74.757973\n",
       "107001    3930  16.470391\n",
       "1999001   1952   8.180713\n",
       "301001      99   0.414903\n",
       "102001      12   0.050291\n",
       "106001      10   0.041909\n",
       "101002       9   0.037718\n",
       "101001       5   0.020955\n",
       "102002       3   0.012573\n",
       "101009       2   0.008382\n",
       "103003       1   0.004191"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 浏览一次的用户的网页类型ID分析\n",
    "i = h['fullURLId'].value_counts()\n",
    "i = pd.DataFrame(i)\n",
    "print(i)\n",
    "i.rename(columns={'fullURLId':u'个数'},inplace=True)\n",
    "i.index.name = u'网页类型ID'\n",
    "i[u'百分比'] = i[u'个数']/i[u'个数'].sum()*100\n",
    " \n",
    "#保存的表名命名格式为“1_2_k此表功能名称”，此表表示生成的第2张表格，功能为typeID：浏览一次的用户的网页类型ID分析\n",
    "i.to_excel('./tmp/2_3_4typeID.xlsx')\n",
    "i"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>个数</th>\n",
       "      <th>百分比</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>网页类型ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101003</th>\n",
       "      <td>17838.0</td>\n",
       "      <td>74.757973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107001</th>\n",
       "      <td>3930.0</td>\n",
       "      <td>16.470391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999001</th>\n",
       "      <td>1952.0</td>\n",
       "      <td>8.180713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>其他</th>\n",
       "      <td>141.0</td>\n",
       "      <td>0.590922</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              个数        百分比\n",
       "网页类型ID                     \n",
       "101003   17838.0  74.757973\n",
       "107001    3930.0  16.470391\n",
       "1999001   1952.0   8.180713\n",
       "其他         141.0   0.590922"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "j=i[i[u'个数']>100]\n",
    "\n",
    "j.loc[u'其他',u'个数'] = i[i[u'个数']<=100][u'个数'].sum()\n",
    "j.loc[u'其他',u'百分比'] = i[i[u'个数']<100][u'百分比'].sum()\n",
    "j# 浏览一次的用户中浏览的网页类型ID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.5 点击1次用户浏览网页统计(点击数大于100次的）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>点击数</th>\n",
       "      <th>百分比</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>网址</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>http://www.lawtime.cn/info/shuifa/slb/2012111978933.html</th>\n",
       "      <td>224.0</td>\n",
       "      <td>0.009388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>http://www.lawtime.cn/ask/exp/13655.html</th>\n",
       "      <td>154.0</td>\n",
       "      <td>0.006454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>http://www.lawtime.cn/ask/question_925675.html</th>\n",
       "      <td>141.0</td>\n",
       "      <td>0.005909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>http://www.lawtime.cn/ask/exp/8495.html</th>\n",
       "      <td>109.0</td>\n",
       "      <td>0.004568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>http://www.lawtime.cn/info/shuifa/slb/2012111978933_2.html</th>\n",
       "      <td>106.0</td>\n",
       "      <td>0.004442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>其他</th>\n",
       "      <td>23127.0</td>\n",
       "      <td>0.969239</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                        点击数       百分比\n",
       "网址                                                                   \n",
       "http://www.lawtime.cn/info/shuifa/slb/201211197...    224.0  0.009388\n",
       "http://www.lawtime.cn/ask/exp/13655.html              154.0  0.006454\n",
       "http://www.lawtime.cn/ask/question_925675.html        141.0  0.005909\n",
       "http://www.lawtime.cn/ask/exp/8495.html               109.0  0.004568\n",
       "http://www.lawtime.cn/info/shuifa/slb/201211197...    106.0  0.004442\n",
       "其他                                                  23127.0  0.969239"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#　点击1次用户浏览网页统计(点击数大于100次的)\n",
    "k = pd.DataFrame(h['fullURL'].value_counts())\n",
    "k.index.name = u'网址'\n",
    "k.columns = [u'点击数']\n",
    "m = k[k[u'点击数'] > 100]\n",
    "m.loc[u'其他',u'点击数'] = k[k[u'点击数']<=100][u'点击数'].sum()\n",
    "m[u'百分比'] = m[u'点击数']/k[u'点击数'].sum()\n",
    "#保存的表名命名格式为“1_2_k此表功能名称”，此表表示生成的第3张表格，功能为lookMorethan100：点击1次用户浏览网页统计(点击数大于100次的)\n",
    "m.to_excel('./tmp/2_3_5lookMorethan100.xlsx')\n",
    "m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
